﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Data.OleDb;

namespace AcessaDadosBD.Repositorios
{
    public class OperadoraRepository :IRepository<Operadora>
    {
        private IDbCommand Command { get; set; }

        public OperadoraRepository(IDbCommand cmd)
        {
            this.Command = cmd;
        }

        public Operadora GetById(string registro)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "SELECT registroANS,nome,logo FROM tbOperadora WHERE registroANS='" + registro+"'";
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteReader();

                if (reader.Read())
                {
                    Operadora operadora = new Operadora();
                    operadora.Nome = reader["nome"].ToString();
                    operadora.RegistroANS = reader["registroANS"].ToString();
                    operadora.Logo = reader["logo"] != DBNull.Value ? (byte[])reader["logo"] : new byte[] { };
                    return operadora;
                }
                else
                {
                   // throw new Exception("Operadora não encontrada");
                    return null;
                }
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public IQueryable<Operadora> GetAll()
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "SELECT registroANS,nome,logo FROM tbOperadora";
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteReader();

                List<Operadora> list = new List<Operadora>();
                while (reader.Read())
                {
                    Operadora operadora = new Operadora();
                    operadora.Nome = reader["nome"].ToString();
                    operadora.RegistroANS = reader["registroANS"].ToString();
                    operadora.Logo = reader["logo"] != DBNull.Value ? (byte[])reader["logo"] : new byte[] { };

                    list.Add(operadora);
                }
                return list.AsQueryable<Operadora>();
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public int Insert(Operadora entity)
        {
            try
            {
                Command.Connection.Open();

                Command.Parameters.Clear();

                if (entity.Logo != null)
                {
                    Command.CommandText = string.Format("INSERT INTO tbOperadora (registroANS, nome, logo) VALUES ('{0}','{1}',@logo)", entity.RegistroANS, entity.Nome);

                    OleDbParameter parameterLogo = new OleDbParameter();
                    parameterLogo.ParameterName = "@logo";
                    parameterLogo.Value = entity.Logo;
                    Command.Parameters.Add(parameterLogo);
                }
                else
                {
                    Command.CommandText = string.Format("INSERT INTO tbOperadora (registroANS, nome) VALUES ('{0}','{1}')", entity.RegistroANS, entity.Nome);
                }
                Command.ExecuteNonQuery();

                return 0;
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Parameters.Clear();
                Command.Connection.Close();
            }
        }

        public void Delete(Operadora entity)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "DELETE FROM tbOperadora WHERE registroANS='" + entity.RegistroANS + "'";
                Command.CommandType = CommandType.Text;

                Command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public void Update(Operadora entity)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = string.Format("UPDATE tbOperadora SET nome='{0}',logo=@logo WHERE registroANS='{1}'",entity.Nome,entity.RegistroANS);
                Command.CommandType = CommandType.Text;

                Command.Parameters.Clear();

                OleDbParameter parameterLogo = new OleDbParameter();
                parameterLogo.ParameterName = "@logo";
                parameterLogo.Value = entity.Logo;
                Command.Parameters.Add(parameterLogo);

                Command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                ///throw new Exception("Erro ao executar operação: " + e.Message);
                throw e;
            }
            finally
            {
                Command.Parameters.Clear();
                Command.Connection.Close();
            }
        }
    }
}
